﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace BI
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            PopulateTable();
            pivotGridControl1.RefreshData();
            pivotGridControl1.BestFit();
        }
        private void PopulateTable()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT N'Doanh so' Name, A.Ngay Date , SUM(A.SotienNT) Amount \n" +
                                   "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                   "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                   "GROUP BY A.Ngay \n" +
                                   "ORDER BY A.Ngay ";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt1 = new DataTable();
                da.Fill(dt1);

                //cmd.CommandText = "SELECT 'Số lượng' Name, A.Ngay Date , SUM(A.Value1) Amount \n" +
                //                   "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                //                   "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                //                   "GROUP BY A.Ngay \n" +
                //                   "ORDER BY A.Ngay ";
                //DataTable dt = new DataTable();
                //da.Fill(dt);

                cmd.CommandText = "SELECT N'Lai lo' Name, A.Ngay Date , SUM(KM.Value7 - KM.Value2) Amount \n" +
                                 "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                 "				INNER JOIN KhoanMucChungTu KM ON KM.ChungTuID = B.ChungTuID  \n" +
                                 "				INNER JOIN LoaiChungTu C ON C.ID = A.LoaiChungTuID AND C.Ma IN ('HoaDonBanHang', 'HoaDonBanLe') \n" +
                                 "GROUP BY A.Ngay \n" +
                                 "ORDER BY A.Ngay ";
                DataTable dt2 = new DataTable();
                da.Fill(dt2);

                //foreach (DataRow row in dt.Rows)
                //{
                //    string name = row[0].ToString();
                //    DateTime date = (DateTime)row[1];
                //    decimal amount = (decimal)row[2];
                //    dataSet1.Tables["Data"].Rows.Add(new object[] {name, date, amount});
                //}

                foreach (DataRow row in dt1.Rows)
                {
                    string name = row[0].ToString();
                    DateTime date = (DateTime)row[1];
                    decimal amount = (decimal)row[2];
                    dataSet1.Tables["Data"].Rows.Add(new object[] { name, date, amount });
                }

                foreach (DataRow row in dt2.Rows)
                {
                    string name = row[0].ToString();
                    DateTime date = (DateTime)row[1];
                    decimal amount = (decimal)row[2];
                    dataSet1.Tables["Data"].Rows.Add(new object[] { name, date, amount });
                }

                fieldDate1.GroupInterval = DevExpress.XtraPivotGrid.PivotGroupInterval.DateMonth;
                fieldValue1.Visible = false;
                pivotGridControl1.OptionsView.ShowRowTotals = false;
                pivotGridControl1.OptionsView.ShowRowGrandTotals = false;
                fieldDate.CollapseAll();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
	
            //DataTable myTable = dataSet1.Tables["Data"];
            //myTable.Rows.Add(new object[] {"Aaa", DateTime.Today, 7});
            //myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddDays(1), 4 });
            //myTable.Rows.Add(new object[] { "Bbb", DateTime.Today, 12 });
            //myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1), 14 });
            //myTable.Rows.Add(new object[] { "Ccc", DateTime.Today, 11 });
            //myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1), 11 });

            //myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1), 4 });
            //myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1).AddDays(1), 2 });
            //myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddYears(1), 3 });
            //myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1).AddYears(1), 3 });
            //myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddYears(1), 8 });
            //myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1).AddYears(1), 22 });

        }

        private void pivotGridControl1_CustomCellValue(object sender, DevExpress.XtraPivotGrid.PivotCellValueEventArgs e)
        {
            if (ReferenceEquals(e.DataField, fieldVar))
            {
                decimal oldValue = Convert.ToDecimal(e.Value);
                if (oldValue == 0)
                    e.Value = 0;
                else if (oldValue < 0)
                    e.Value = -1;
                else
                    e.Value = 1;
            }

        }
       
    }
}